# -*- coding: utf-8 -*-
'''
Created on 19/dic/2014

@author: Daniel
'''
import sqlite3
from constants                      import constants
from utils.utils                    import getModulePath
from utils.utils                    import computePath
from utils.utils                    import exceptionManagement
import sys
import os
import logging

class Browse(object):
    def __init__(self):
        self.children = []
        self.browser_sites = []

    def addChildren(self, children=[]):
        for child in children:
            self.addChild(child)
            
    def addChild(self, childToAppend):
        found = False
        for childObj in self.children:
            if childObj.menu_name == childToAppend.menu_name and childObj.parent_id == childToAppend.parent_id:
                found = True
        if not found:
            self.children.append(childToAppend)
        
        
    def addBrowserSites(self, sitesObjs=[]):
        for siteObj in sitesObjs:
            self.addBrowserSite(siteObj)
            
    def addBrowserSite(self, siteObj):
        found = False
        for siteStoredObj in self.browser_sites:
            if siteObj.name == siteStoredObj.name:
                found = True
                break
        if not found:
            self.browser_sites.append(siteObj)

class BrowserSiteObj(object):
    def __init__(self, name=''):
        self.name = name


class psqlConnections(object):
    def __init__(self):
        super(psqlConnections,self).__init__()
    
    def setConnection(self, dbName='customDb'):
        try:
            dbPath = os.path.join(getModulePath(), dbName).encode(sys.getfilesystemencoding())
            logging.debug('setConnection: dbPath: %s' % (dbPath))
            self.connection = sqlite3.connect(dbPath)
            self.cur = self.connection.cursor()
            self.saveConnection()
        except Exception,ex:
            self.exceptionManagement(ex, 'Connessione Database fallita')
        
    def exceptionManagement(self, ex, message=''):
        exceptionManagement(ex, message)
        
    def saveConnection(self):
        constants.CONNECTION = self.connection
        constants.CURSOR = self.cur
#         
    def tableExistOrCreate(self, tableName, columns):
        try:
            if not self.tableExist(tableName):
                self.createTable(tableName, columns)
            else:
                self.addFieldsToTable(columns, tableName)
        except Exception,ex:
            self.exceptionManagement(ex, 'Table exist or create failed')
#     
    def tableExist(self,tableName):
        try:
            if constants.CURSOR:
                cur = constants.CURSOR
                toExec="""SELECT name FROM sqlite_master WHERE type="table" AND name='%s';"""%unicode(tableName.lower())
                cur.execute(toExec)
                tables=cur.fetchall()
                if len(tables) == 0:
                    return False
                else:
                    return True
        except Exception,ex:
            self.exceptionManagement(ex, 'Table exist failed')
             
    def getDefaultByType(self, fieldType):
        if 'VARCHAR' in fieldType:
            return "''"
        elif 'INT' in fieldType:
            return 0
        elif 'BOOL' in fieldType:
            return False
        
    def createTable(self,tableName, columns):
        try:
            toExec = "create table %s (id INTEGER PRIMARY KEY ASC,"%(tableName.lower())
            for fieldName in columns.keys():
                ftype = columns.get(fieldName,False)
                if not ftype:
                    ftype = 'varchar(255)'
                default = self.getDefaultByType(ftype)
                toExec = toExec + ' %s %s DEFAULT %s,'%(fieldName, ftype, default)
            toExec = toExec[0:-1] +");"
            constants.CURSOR.execute(toExec)
            constants.CONNECTION.commit()
        except Exception,ex:
            self.exceptionManagement(ex, 'Table creation failed')

    def addFieldsToTable(self, fieldsDict, tableName):
        '''
        fieldsDict = {'fieldName':'fieldType',...}
        To add not written columns to a table
        '''
        if self.tableExist(tableName):
            toExec = "PRAGMA table_info(%s)"%(tableName)
            constants.CURSOR.execute(toExec)
            toCompLst = constants.CURSOR.fetchall()
            writtenColumnNames = []
            for tupleToComp in toCompLst:
                writtenColumnNames.append(tupleToComp[1])
            for fieldName, fieldType in fieldsDict.items():
                if fieldName not in writtenColumnNames:
                    toExec = 'ALTER TABLE %s ADD '%(tableName)
                    if not fieldType:
                        fieldType = 'varchar(255)'
                    default = self.getDefaultByType(fieldType)
                    toExec = toExec+'%s %s DEFAULT %s'%(fieldName, fieldType, default)
                    constants.CURSOR.execute(toExec)
                    constants.CONNECTION.commit()
        
    def writeRows(self, rowsList, table):
        for rowDict in rowsList:
            self.writeRow(rowDict, table)
            
    def writeRow(self, row, table, valuesToVerify=[]): #row = {'key':'value'}
        try:
            if valuesToVerify:
                filterList = []
                for field in valuesToVerify:
                    condition = (field,'=', row[field])
                    filterList.append(condition)
                res = self.search(filterList, table)
                if res:
                    self.updateRow(row, table, [('id', '=', res[0])])
                    return res[0]
            toExec = """INSERT INTO %s("""%(table.lower())
            for k in row.keys():
                toExec = toExec+"%s,"%k
            toExec = toExec[0:-1] +""") VALUES("""
            for v in row.values():
                toExec = toExec+""" '%s',"""%self.computeValue(unicode(v))
            toExec = toExec[0:-1] +");"
            cur = constants.CURSOR.execute(toExec)
            id = cur.lastrowid
            constants.CONNECTION.commit()
            return id
        except Exception,ex:
            self.exceptionManagement(ex, 'Write row failed')

    def search(self, filterList, table):
        try:
            commonExec = """SELECT ID FROM %s""" % (table.lower())
            if not filterList:
                toExec = commonExec
            else:
                toExec =  self.computeFilter(commonExec, filterList) + ' ;'
#                 toExec = """SELECT ID FROM %s WHERE """%(table.lower())
#                 for colName, operator, value in filterList:
#                     toExec = toExec+"%s %s '%s' AND "%(unicode(colName),unicode(operator),unicode(value))
#                 toExec = toExec[:-4]+' ;'
            cur =constants.CURSOR
            cur.execute(toExec)
            toCompLst = cur.fetchall()
            outList = []
            for tupleObj in toCompLst:
                outList.append(tupleObj[0])
            return outList
        except Exception, ex:
            self.exceptionManagement(ex, 'Search failed')
         
    def toUnicode(self, val):
        return unicode(val)
       
    def createOrUpdate(self, valuesToUpdate, table, condition=[]):
        '''
            Condition is [('fieldname', 'operator', 'fieldvalue')] and
            is used to know if a records already exists.
        '''
        if condition:
            foundIds = self.search(condition, table)
            if foundIds:
                return self.updateRow(valuesToUpdate, table, [('id', '=', foundIds[0])])
        return self.writeRow(valuesToUpdate, table)
        
    def updateRow(self, valuesToUpdate, table, condition ): #{'column':'value'}  ('cc','in','ff')
        '''
        UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
        '''
        try:
            toExec = "UPDATE %s SET"%(table)
            for key, val in valuesToUpdate.items():
                toExec = toExec+""" %s = '%s',"""%(unicode(key),self.computeValue(unicode(val)))
            toExec = toExec[0:-1]
            toExec = self.computeFilter(toExec, condition)
            constants.CURSOR.execute(toExec)
            constants.CONNECTION.commit()
        except Exception,ex:
            self.exceptionManagement(ex, 'Update row failed')

    def getRowsTable(self, table, fields=[], filtersList=[]):
        try:
            toExec = "select "
            if not fields:
                toExec = toExec+'* '
            else:
                for field in fields:
                    toExec = toExec+unicode(field)+','
            toExec = toExec[:-1]
            toExec = toExec+" from %s"%(table.lower())
            if filtersList:
                toExec = self.computeFilter(toExec, filtersList)
            toExec = toExec + ';'
            cur =constants.CURSOR
            cur.execute(toExec)
            rows=cur.fetchall()
            for row in rows:
                newRow = []
                for value in row:
                    newValue = self.decomputeValue(value)
                    newRow.append(newValue)
                index = rows.index(row)
                rows[index] = newRow
            return self.convertToObject(rows, table, fields)
        except Exception,ex:
            self.exceptionManagement(ex, 'Get rows from table failed')
            
    def computeFilter(self, toExec, filtersList):
        toExec = toExec+' where '
        for filterr in filtersList:
            if len(filterr)==3:
                fieldName   = filterr[0]
                operator    = filterr[1]
                match       = filterr[2]
                if isinstance(match, list) or isinstance(match, tuple):
                    if len(match) == 1:
                        match = "('" + str(match[0]) + "')"
                    elif len(match) == 0:
                        match = '()'
                    else:
                        toreplace = '('
                        for elem in match:
                            toreplace = toreplace + "'%s'," % (str(elem))
                        toreplace = toreplace[:-1] + ')'
                        match = toreplace
                    toExec = toExec+"""%s %s %s """%(unicode(fieldName),unicode(operator),match)
                else:
                    toExec = toExec+"""%s %s '%s' """%(unicode(fieldName),unicode(operator),unicode(match))
                if len(filtersList)>1:
                    toExec = toExec+""" and """
        if len(filtersList)>1:
            toExec = toExec[:-5]
        return toExec
        
    def getHeadersFromTable(self, tableName):
        def computeResult(result):
            outList = []
            for fieldTuple in result:
                outList.append(fieldTuple[1])
            return outList
        toExec = 'PRAGMA table_info(%s)'%(tableName)
        cur =constants.CURSOR
        cur.execute(toExec)
        rows=cur.fetchall()
        res = []
        if isinstance(rows,list) and len(rows)>0:
            res = computeResult(rows)
        return res
        
    def convertToObject(self, rows, tableName, toGet):
        listToReturn = []
        tableOrderedHeaders = self.getHeadersFromTable(tableName)
        if toGet:
            tableOrderedHeaders = toGet
        for row in rows:
            computedDict = dict(zip(tableOrderedHeaders,row))
            brws = Browse()
            for var, value in computedDict.items():
                brws.__dict__[var] = value
                pass
            listToReturn.append(brws)
        return listToReturn

    def computeValue(self, value):
        try:
            if isinstance(value, unicode):
                value = value.replace("\'","'")
                value = value.replace("'",constants.REPLACE_CHARACTER)
                return value
        except Exception,ex:
            self.exceptionManagement(ex, 'Compute value for db failed.')
         
    def decomputeValue(self, value):
        try:
            if isinstance(value, unicode):
                value = value.replace(constants.REPLACE_CHARACTER,"'")
                return value
            return value
        except Exception,ex:
            self.exceptionManagement(ex, 'Decompute value from db failed.')
            
    def deleteRowsDb(self, ids, table):
        for localId in ids:
            self.deleteRowdb(localId, table)
            
    def deleteRowdb(self, rowID, table):
        try:
            toExec = "DELETE FROM %s WHERE Id = %s ;"%(table.lower(), rowID)
            cur = constants.CURSOR
            cur.execute(toExec)
            constants.CONNECTION.commit()
        except Exception, ex:
            self.exceptionManagement(ex, 'Delete Row Failed.')
